<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjipro extends Common
{
    //产品决策依据
    public function jcyj(){
        return $this->fetch();
    }
   //门店条码报表
    public function tiaoma($page=1,$list=10){
        $request = Request::instance();
        $data=$request->param();
        $wherenet="n.isdel=0 ";
        $wherepro="1=1 ";
        if($data){
            if(trim($data["prono"])){
                $prono=trim($data["prono"]);
                $wherepro.="and p.pro_no='{$prono}' ";
            }
            if(trim($data["proname"])){
                $proname=trim($data["proname"]);
                $wherepro.="and p.pro_name='{$proname}' ";
            }
            if(trim($data["prosku"])){
                $prosku=trim($data["prosku"]);
                $wherepro.="and p.pro_sku='{$prosku}' ";
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $wherenet.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $wherenet.="and p.title='{$sheng}' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $wherenet.="and c.title='{$shi}' ";
            }
            if(trim($data["brand"])){
                $brand=trim($data["brand"]);
                $wherepro.="and b.brand_name='{$brand}' ";
            }
            if(trim($data["category"])){
                $category=trim($data["category"]);
                $wherepro.="and c.category_name='{$category}' ";
            }
            if(trim($data["series"])){
                $series=trim($data["series"]);
                $wherepro.="and s.series='{$series}' ";
            }
            if(trim($data["sku"])){
                $sku=trim($data["sku"]);
                $wherepro.="and sk.sku_name='{$sku}' ";
            }
        }else{
            $data=["prono"=>"","proname"=>"","prosku"=>"","fenqu"=>"","sheng"=>"","shi"=>"","brand"=>"","category"=>"","series"=>"","sku"=>""];
        }
        //获得门店数
        $allnet=db("cg_net n")->join("cg_system s","n.system_id=s.id")
        ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_city c","c.id=j.city")
        ->join("cg_market_provice p","p.id=j.provice")->join("cg_market_zoning z","z.id=j.zoning")
        ->where($wherenet)->field("n.id")->select();
        $netarr=[];
        foreach ($allnet as $key => $value) {
            # code...
            $netarr[]=$value["id"];
        }
        $netstr=implode(',',$netarr);
        //产品
        $sum=db("pro_brands b")->join("pro_category c","c.brand_id=b.id")->join("pro_series s","s.category_id=c.id")
        ->join("pro_sku sk","sk.series_id=s.id")->join("pro_products p","p.sku_id=sk.id")->where($wherepro)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=db("pro_brands b")->join("pro_category c","c.brand_id=b.id")->join("pro_series s","s.category_id=c.id")
        ->join("pro_sku sk","sk.series_id=s.id")->join("pro_products p","p.sku_id=sk.id")->where($wherepro)
        ->order("p.id")
        ->field("p.id,p.pro_no,p.pro_name,p.pro_sku,p.weight,p.box_num,p.unit,p.remark")->limit($start,$list)
        ->select();
        for($i=0;$i<count($apply);$i++){
            if($netstr){
                $zc=db("cg_net_barcode")->where("prod={$apply[$i]["id"]} and net_id in ($netstr) and isdel=0 and tuichang=0")->count();
                if($zc){
                    $apply[$i]["zc"]=$zc;
                }else{
                    $apply[$i]["zc"]=0;
                }
                $apply[$i]["sm"]=0;
                $qc=db("cg_net_barcode")->where("prod={$apply[$i]["id"]} and net_id in ($netstr) and isdel=0 and tuichang=1")->count();
                if($qc){
                    $apply[$i]["qc"]=$qc;
                }else{
                    $apply[$i]["qc"]=0;
                }
                $apply[$i]["wjc"]=count($allnet)-$apply[$i]["zc"];
            }else{
                $apply[$i]["zc"]=0;
                $apply[$i]["sm"]=0;
                $apply[$i]["qc"]=0;
                $apply[$i]["wjc"]=0;
            }
        }
        $this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        return $this->fetch();
    }
    public function tiaomadc(){
        $wherenet="n.isdel=0 ";
        $wherepro="1=1 ";
         //获得门店数
        $allnet=db("cg_net n")->join("cg_system s","n.system_id=s.id")
        ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_city c","c.id=j.city")
        ->join("cg_market_provice p","p.id=j.provice")->join("cg_market_zoning z","z.id=j.zoning")
        ->where($wherenet)->field("n.id")->select();
        $netarr=[];
        foreach ($allnet as $key => $value) {
            # code...
            $netarr[]=$value["id"];
        }
        $netstr=implode(',',$netarr);
         $apply=db("pro_brands b")->join("pro_category c","c.brand_id=b.id")->join("pro_series s","s.category_id=c.id")
        ->join("pro_sku sk","sk.series_id=s.id")->join("pro_products p","p.sku_id=sk.id")->where($wherepro)
        ->order("p.id")
        ->field("p.id,p.pro_no,p.pro_name,p.pro_sku,p.weight,p.box_num,p.unit,p.remark")
        ->select();
        for($i=0;$i<count($apply);$i++){
            if($netstr){
                $zc=db("cg_net_barcode")->where("prod={$apply[$i]["id"]} and net_id in ($netstr) and isdel=0 and tuichang=0")->count();
                if($zc){
                    $apply[$i]["zc"]=$zc;
                }else{
                    $apply[$i]["zc"]=0;
                }
                $apply[$i]["sm"]=0;
                $qc=db("cg_net_barcode")->where("prod={$apply[$i]["id"]} and net_id in ($netstr) and isdel=0 and tuichang=1")->count();
                if($qc){
                    $apply[$i]["qc"]=$qc;
                }else{
                    $apply[$i]["qc"]=0;
                }
                $apply[$i]["wjc"]=count($allnet)-$apply[$i]["zc"];
            }else{
                $apply[$i]["zc"]=0;
                $apply[$i]["sm"]=0;
                $apply[$i]["qc"]=0;
                $apply[$i]["wjc"]=0;
            }
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("门店条码报表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "门店条码报表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:L2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"产品编码");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"产品条形码");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"重量");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"箱包装");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"单位");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"正常门店数");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"锁码门店数");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"退场门店数");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"未进场门店数");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"备注");
        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["pro_no"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["pro_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["pro_sku"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["weight"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["box_num"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["unit"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["zc"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["sm"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["qc"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["wjc"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["remark"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:L'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //sku发运排名
    public function skufy($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $this->assign("cn",$cn);
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        $where=" f.fyDate>='{$cst}' and f.fyDate<='{$ced}' ";
        $request = Request::instance();
        $data=$request->param();
        if($data){
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='$fenqu' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='$sheng' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='$shi' ";
            }
            if(trim($data["cate"])){
                $cate=trim($data["cate"]);
                $where.="and pc.category_name='$cate' ";
            }
            if(trim($data["sku"])){
                $sku=trim($data["sku"]);
                $where.="and sk.sku_name='$sku' ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","cate"=>"","sku"=>""];
        }
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
        ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
        ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
        ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
        ->where($where)->group("sk.id")
        ->field("sum(f.fyZheMoney) as zjine,sk.sku_name,pc.category_name,sk.id")->select();

        $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
                ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
                ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
                ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
                ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
                ->where("sk.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zjine"];
       }
       $this->assign("zong",$zong);
       $this->assign("apply",$apply);
       $this->assign("kaohe2",$kaohe2);
       $this->assign("data",$data);
        return $this->fetch();
    }

    public function skufydc($cid){
         $cn=db("cg_cainian")->where(["id"=>$cid])->find();
       
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        $where=" f.fyDate>='{$cst}' and f.fyDate<='{$ced}' ";
        
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
        ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
        ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
        ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
        ->where($where)->group("sk.id")
        ->field("sum(f.fyZheMoney) as zjine,sk.sku_name,pc.category_name,sk.id")->select();

        $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
                ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
                ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
                ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
                ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
                ->where("sk.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zjine"];
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$cn["title"]}年SKU发运排名");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$cn["title"]}年SKU发运排名");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"产品品类");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"产品sku");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"小计");
        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["category_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sku_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $h++;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':C'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kaohe2[0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kaohe2[1]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe2[2]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kaohe2[3]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$kaohe2[4]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$kaohe2[5]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$kaohe2[6]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$kaohe2[7]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$kaohe2[8]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$kaohe2[9]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$kaohe2[10]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$kaohe2[11]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);

         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //单品发运排名
    public function profy($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $this->assign("cn",$cn);
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        $where=" f.fyDate>='{$cst}' and f.fyDate<='{$ced}' ";
        $request = Request::instance();
        $data=$request->param();
        if($data){
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='$fenqu' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='$sheng' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='$shi' ";
            }
            if(trim($data["cate"])){
                $cate=trim($data["cate"]);
                $where.="and pc.category_name='$cate' ";
            }
            if(trim($data["pro"])){
                $pro=trim($data["pro"]);
                $where.="and pp.pro_name='$pro' ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","cate"=>"","pro"=>""];
        }
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
        ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
        ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
        ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
        ->where($where)->group("pp.id")
        ->field("sum(f.fyZheMoney) as zjine,pp.pro_name,pc.category_name,sk.id")->select();

        $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
                ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
                ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
                ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
                ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
                ->where("pp.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zjine"];
       }
       $this->assign("zong",$zong);
       $this->assign("apply",$apply);
       $this->assign("kaohe2",$kaohe2);
       $this->assign("data",$data);
        return $this->fetch();
    }
    public function profydc($cid){
         $cn=db("cg_cainian")->where(["id"=>$cid])->find();
       
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        $where=" f.fyDate>='{$cst}' and f.fyDate<='{$ced}' ";
        
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
        ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
        ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
        ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
        ->where($where)->group("pp.id")
        ->field("sum(f.fyZheMoney) as zjine,pp.pro_name,pc.category_name,sk.id")->select();

        $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
                ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
                ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
                ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
                ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
                ->where("pp.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zjine"];
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$cn["title"]}年单品发运排名");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$cn["title"]}年单品发运排名");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"产品品类");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"产品名称");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"小计");
        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["category_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["pro_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $h++;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':C'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kaohe2[0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kaohe2[1]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe2[2]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kaohe2[3]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$kaohe2[4]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$kaohe2[5]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$kaohe2[6]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$kaohe2[7]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$kaohe2[8]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$kaohe2[9]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$kaohe2[10]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$kaohe2[11]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);

         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //单品发运汇总
    public function profyhz(){

        $where="1=1 ";
        $request = Request::instance();
        $data=$request->param();
        if($data){
             if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='$fenqu' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='$sheng' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='$shi' ";
            }
            if(trim($data["jxssn"])){
                $jxssn=trim($data["jxssn"]);
                $where.="and j.sn='$jxssn' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='$jxs' ";
            }
            if(trim($data["netsn"])){
                $netsn=trim($data["netsn"]);
                $nid=db("cg_net")->where(["sn"=>$netsn])->value("id");
                if($nid){
                    $where.="and f.sdNet_id=$nid ";
                }else{
                    $where.="and 1<>1 ";
                }
            }
            if(trim($data["net"])){
                $net=trim($data["net"]);
                $nid=db("cg_net")->where(["title"=>$net])->value("id");
                if($nid){
                    $where.="and f.sdNet_id=$nid ";
                }else{
                    $where.="and 1<>1 ";
                }
            }
            if(trim($data["cate"])){
                $cate=trim($data["cate"]);
                $where.="and pc.category_name='$cate' ";
            }
            if(trim($data["series"])){
                $series=trim($data["series"]);
                $where.="and ps.series_name='$series' ";
            }
            if(trim($data["sku"])){
                $sku=trim($data["sku"]);
                $where.="and sk.sku_name='$sku' ";
            }
            if(trim($data["prono"])){
                $prono=trim($data["prono"]);
                $where.="and pp.pro_no='$prono' ";
            }
            if(trim($data["pro"])){
                $pro=trim($data["pro"]);
                $where.="and pp.pro_name='$pro' ";
            }
            if($data["start"]){
                $where.="and f.fyDate>='{$data["start"]}' ";
            }
            if($data["end"]){
                $where.="and f.fyDate<='{$data["end"]}' ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","jxssn"=>"","jxs"=>"","netsn"=>"","net"=>"","cate"=>"","series"=>"","sku"=>"","prono"=>"","pro"=>"","start"=>"","end"=>""];
        }
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
        ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
        ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
        ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
        ->group("pp.id")->where($where)
        ->order("pc.id,sk.id,pp.id")
        ->field("pp.id,pp.pro_name,sk.sku_name,pc.category_name,pp.unit,pp.format,sum(f.fyZheMoney) as money")->select();
        $zongsong=0;$zongmai=0;$zongmoney=0;
        for($i=0;$i<count($apply);$i++){

            $song=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
            ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
            ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
            ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
            ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
            ->where($where."and pp.id={$apply[$i]["id"]} and fyType=1")->sum("fyNum");
            if($song){
                $apply[$i]["song"]=$song;
                $zongsong+=$song;
            }else{
                $apply[$i]["song"]=0;
            }
            $mai=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")
            ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","p.id=j.provice")
            ->join("cg_market_city c","c.id=j.city")->join("pro_products pp","f.product_id=pp.id","LEFT")
            ->join("pro_sku sk","pp.sku_id=sk.id","LEFT")->join("pro_series ps","sk.series_id=ps.id","LEFT")
            ->join("pro_category pc","ps.category_id=pc.id","LEFT")->join("pro_brands pb","pc.brand_id=pb.id","LEFT")
            ->where($where."and pp.id={$apply[$i]["id"]} and fyType=0")->sum("fyNum");
            if($mai){
                $apply[$i]["mai"]=$mai;
                $zongmai+=$mai;
            }else{
                $apply[$i]["mai"]=0;
            }
            $zongmoney+=$apply[$i]["money"];
        }
        $this->assign("zongsong",$zongsong);
        $this->assign("zongmai",$zongmai);
        $this->assign("zongmoney",$zongmoney);
        $this->assign("apply",$apply);
        $this->assign("data",$data);
        return $this->fetch();
    }
}